# coding=utf-8
from flask import Flask, render_template, request, redirect
from pymysql import Connect
from flask_cors import CORS

app = Flask("__name__")


# 全局解决跨域问题
CORS(app, supports_credentials=True)

# 连接数据库
db_connect = Connect(
    user="root",
    password="123456",
    host="192.168.139.129",
    database="sms",
    port=3306,
    charset="utf8",
)


# 查询数据
def search_data(sql):
    # 获取游标
    cursor = db_connect.cursor()
    cursor.execute(sql)
    # 获取表头字段
    dec = cursor.description
    # 获取所有的查询结果
    result = cursor.fetchall()
    # 表头字段存储到列表中
    list_dec = []
    # 读取到的结果数据存储列表中
    list_result = []
    # 遍历表头字段，存储到列表中
    for i in range(0, len(dec)):
        list_dec.append(dec[i][0])
    # 遍历结果的每条数据
    for j in range(0, len(result)):
        # 遍历完一条数据，重置map
        map = {}
        # 遍历每个数据
        for x in range(0, len(result[j])):
            # 存储到字典中
            map[list_dec[x]] = str(result[j][x])
        # 字典添加到列表中
        list_result.append(map)
    # 关闭游标
    cursor.close()
    return list_result


# 修改数据
def change_data(sql):
    # 获取游标
    cursor = db_connect.cursor()
    # 执行slq语句
    cursor.execute(sql)
    # 提交数据
    db_connect.commit()
    # 关闭游标和连接
    cursor.close()


# 首页
@app.route("/")
def index():
    return render_template("index.html")


# 查询接口，获取所有的图书信息
@app.route("/bookinfo")
def book_info():
    sql_str = """select * from books;"""
    book_info = search_data(sql_str)
    return book_info


# 添加接口
@app.route("/add",methods=["POST","GET"])
def add():
    if request.method =="GET":
        return render_template("add.html")
    if request.method =="POST":
        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")
        sql_str = f"""insert into books(name,price,summary,quantity) values('{name}',{price},'{summary}',{quantity});"""
        print("添加图书的sql语句：",sql_str)
        change_data(sql_str)
        return redirect("/")


# 修改接口
@app.route("/change/<int:bid>",methods=["POST","GET"])
def change(bid):
    if request.method =="GET":
        return render_template("change.html")
    if request.method =="POST":
        # bid = request.values.get("bid")
        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")
        sql_str = f"""update books set name='{name}',price={price},summary='{summary}',quantity={quantity} where bid={bid};"""
        print("修改图书的sql语句：",sql_str)
        change_data(sql_str)
        return redirect("/")


# 删除接口
@app.route("/delete/<int:bid>")
def delete(bid):
    sql_str = f"""delete from books where bid={bid};"""
    print("通过bid删除图书的sql语句：",sql_str)
    change_data(sql_str)
    return redirect("/")


# 通过bid查询修改的的图书信息
@app.route("/select/<int:bid>")
def search_by_bid(bid):
    sql_str = f"""select * from books where bid={bid};"""
    print("通过bid查询图书的sql语句：",sql_str)
    book_info = search_data(sql_str)[0]
    return book_info


# 通过name,summary 模糊查询图书信息
@app.route("/search")
def search():
    # name =request.args.get("name")
    # summary =request.args.get("summary")
    key = request.args.get('key')
    sql_str = f"""select * from books where name like '%{key}%' or summary like '%{key}%';"""
    print("通过name或者summary模糊查询图书：",sql_str)
    book_info = search_data(sql_str)
    return book_info




if __name__ == '__main__':
    app.run(debug=True)


